package org.hao.samples.util;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hao.samples.model.StudentVO;

public class ExcelUtils {

	public static String OFFICE_EXCEL_2007_POSTFIX = ".xlsx";
	public static String OFFICE_EXCEL_2003_POSTFIX = ".xls";

	/**
	 * 根据inputStream和excel类型创建excel workbook
	 * 
	 * @param is
	 * @param postfix
	 * @return
	 */
	public static Workbook openWorkbook(InputStream is, String postfix) {
		Workbook hssfWorkbook = null;
		try {
			if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
				hssfWorkbook = new HSSFWorkbook(is);
			} else if (OFFICE_EXCEL_2007_POSTFIX.equals(postfix)) {
				hssfWorkbook = new XSSFWorkbook(is);
			}
		} catch (IOException e) {
			// if(e instanceof NotOLE2Filexception){
			// return null;
			// }
			e.printStackTrace();
		}
		return hssfWorkbook;
	}

	public static List<StudentVO> getStudentListByExcel(Workbook wk) {
		List<StudentVO> resultList = new ArrayList<StudentVO>();
		Sheet sheet = wk.getSheetAt(0);
		int maxCellNubmer = 0;
		int maxRowNumber = sheet.getLastRowNum();
		StudentVO vo = null;
		for (int currentRowNumber = 1; currentRowNumber <= maxRowNumber; currentRowNumber++) {
			Row row = sheet.getRow(currentRowNumber);
			maxCellNubmer = row.getLastCellNum();
			vo = new StudentVO();
			for (int currentCellNumber = 0; currentCellNumber < maxCellNubmer; currentCellNumber++) {
				Cell cell = row.getCell(currentCellNumber);
				switch(currentCellNumber){
					case 0:
						vo.setUser_id(new Integer(getValue(cell)));
						break;
					case 1:
						vo.setPassword(getValue(cell));
						break;
					case 2:
						vo.setUser_name((String) getValue(cell));
						break;
					case 3:
						vo.setGender(new Integer(getValue(cell)));
						break;
					case 4:
						vo.setTel(getValue(cell));
						break;
					case 5:
						vo.setAddress(getValue(cell));
						break;
				}
			}
			resultList.add(vo);
		}

		return resultList;
	}

	@SuppressWarnings("static-access")
	private static String getValue(Cell cell){
		if(cell == null){
			return "";
		}
		if(cell.getCellType() == cell.CELL_TYPE_BOOLEAN){
			return String.valueOf(cell.getBooleanCellValue());
		} else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC){
			DecimalFormat df = new DecimalFormat("0");
			return df.format(cell.getNumericCellValue());
		} else {
			String str = cell.getStringCellValue();
			String string = String.valueOf(str);
			return string;
		}
	}
}
